"""
Case Type   : PACKAGE
Case Name   : 用户赋权create any package,分别在用户模式和public模式下创建package
Create At   : 2022/04/26
Owner       : liu-tong-8848
Description :
    1.创建用户
    2.用户赋权create any package
    3.新用户身份进入数据库,切换当前模式为public,声明并定义package1
    4.查询package所属模式
    5.新用户身份进入数据库,声明并定义package2
    6.查询package所属模式
    7.清理环境
Expect      :
    1.成功
    2.成功
    3.成功
    4.返回public
    5.成功
    6.返回用户模式
    7.成功
History     :
"""

import os
import unittest
from yat.test import Node
from yat.test import macro
from testcase.utils.Constant import Constant
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Logger import Logger


class PACKAGE(unittest.TestCase):
    def setUp(self):
        self.logger = Logger()
        self.primary_node = Node('PrimaryDbUser')
        self.primary_sh = CommonSH('PrimaryDbUser')
        self.Con = Constant()
        self.package = 'p_test_0035_01'
        self.package1 = 'p_test_0035_02'
        self.procedure = 'p_package_0035_01'
        self.procedure1 = 'p_package_0035_02'
        self.user = 'u_package_0035'
        self.table = 't_package_0035'

    def test_package(self):
        self.logger.info(f'-----{os.path.basename(__file__)} start-----')
        step = 'step1:创建用户 expect:成功'
        self.logger.info(step)
        create_user = self.primary_sh.execut_db_sql(
            f'''drop user if exists {self.user} cascade;
            create user {self.user} with sysadmin identified by '{macro.PASSWD_REPLACE}';''')
        self.logger.info(create_user)
        self.assertIn(self.Con.CREATE_ROLE_SUCCESS_MSG, create_user,
                      '执行失败' + step)

        step = 'step2:用户赋权create any package expect:成功'
        self.logger.info(step)
        grant_privi = self.primary_sh.execut_db_sql(
            f'''grant create any package to {self.user};GRANT CREATE ON DATABASE {self.primary_node.db_name} TO {self.user};''')
        self.logger.info(grant_privi)
        self.assertIn('GRANT', grant_privi, '执行失败' + step)

        step = 'step3:新用户身份进入数据库,切换当前模式为public,' \
               '声明并定义package1 expect:成功'
        self.logger.info(step)
        sepc_def_pack = self.primary_sh.execut_db_sql(
            f'''set current_schema = public;
            create or replace package {self.package} is
            var1 int:=1;
            var2 int:=2;
            procedure {self.procedure}();
            end {self.package};
            create or replace package body {self.package} is
            procedure {self.procedure}()
            is
            begin
            drop table if exists {self.table};
            create table {self.table}(c_int int);
            insert into {self.table} values(var1);
            insert into {self.table} values(var2);
            end;
            end {self.package};''',
            sql_type=f" -U {self.user} -W {macro.PASSWD_REPLACE}")
        self.logger.info(sepc_def_pack)
        self.assertIn('SET', sepc_def_pack, '执行失败' + step)
        self.assertIn(self.Con.create_package_success_msg, sepc_def_pack,
                      '执行失败' + step)
        self.assertIn(self.Con.create_package_body_success_msg, sepc_def_pack,
                      '执行失败' + step)

        step = 'step4:查询package1所属模式 expect:返回public模式'
        self.logger.info(step)
        select_schema = self.primary_sh.execut_db_sql(
            f'''select nspname from pg_namespace where oid = 
            (select pkgnamespace from gs_package where pkgname = 
            '{self.package}');''')
        self.logger.info(select_schema)
        self.assertIn('public', select_schema, '执行失败' + step)

        step = 'step5:新用户身份进入数据库,声明并定义package2 expect:成功'
        self.logger.info(step)
        sepc_def_pack = self.primary_sh.execut_db_sql(
            f'''create or replace package {self.package1} is
             var1 int:=1;
             var2 int:=2;
             procedure {self.procedure1}();
             end {self.package1};
             create or replace package body {self.package1} is
             procedure {self.procedure1}()
             is
             begin
             drop table if exists {self.table};
             create table {self.table}(c_int int);
             insert into {self.table} values(var1);
             insert into {self.table} values(var2);
             end;
             end {self.package1};''',
            sql_type=f" -U {self.user} -W {macro.PASSWD_REPLACE}")
        self.logger.info(sepc_def_pack)
        self.assertIn(self.Con.create_package_success_msg, sepc_def_pack,
                      '执行失败' + step)
        self.assertIn(self.Con.create_package_body_success_msg, sepc_def_pack,
                      '执行失败' + step)

        step = 'step6:查询package2所属模式 expect:返回用户模式'
        self.logger.info(step)
        select_schema = self.primary_sh.execut_db_sql(
            f'''select nspname from pg_namespace where oid = 
            (select pkgnamespace from gs_package where pkgname = 
            '{self.package1}');''')
        self.logger.info(select_schema)
        self.assertIn(self.user, select_schema, '执行失败' + step)

    def tearDown(self):
        step = 'step7:清理环境 expect:清理环境成功'
        self.logger.info(step)
        clean_environment1 = self.primary_sh.execut_db_sql(
            f'''drop package {self.package1};''',
            sql_type=f" -U {self.user} -W {macro.PASSWD_REPLACE}")
        self.logger.info(clean_environment1)
        clean_environment2 = self.primary_sh.execut_db_sql(
            f'''set current_schema = public;
            drop package {self.package};''',
            sql_type=f" -U {self.user} -W {macro.PASSWD_REPLACE}")
        self.logger.info(clean_environment2)
        clean_environment3 = self.primary_sh.execut_db_sql(
            f'''drop user {self.user} cascade;''')
        self.logger.info(clean_environment3)
        self.assertIn(self.Con.drop_package_success_msg, clean_environment1,
                      '执行失败' + step)
        self.assertTrue('SET' in clean_environment2 and
                        self.Con.drop_package_success_msg in
                        clean_environment2 , '执行失败' + step)
        self.assertIn(self.Con.DROP_ROLE_SUCCESS_MSG, clean_environment3,
                      '执行失败' + step)
        self.logger.info(f'-----{os.path.basename(__file__)} end-----')
